{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import os\n",
    "import matplotlib.font_manager as fm\n",
    "import pandas as pd\n",
    "from sklearn.linear_model import LinearRegression\n",
    "from sklearn.model_selection import train_test_split\n",
    "import pandas as pd\n",
    "from statsmodels.tsa.arima.model import ARIMA\n",
    "import matplotlib.pyplot as plt\n",
    "import matplotlib.pyplot as plt\n",
    "import matplotlib\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "from docx import Document"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 指定中文字体\n",
    "matplotlib.rcParams['font.sans-serif'] = ['SimHei']\n",
    "# 步骤1：读取数据\n",
    "original= pd.read_excel('./附件3.xlsx')\n",
    "\n",
    "# 按照时间顺序排序\n",
    "original.sort_values('日期', inplace=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 使用pivot_table将时间作为列，单品编码作为行，并将每个单品编码在该时间下的成本加成定价作为值\n",
    "pivot_table = original.pivot_table(index='日期', columns='单品编码', values='批发价格(元/千克)')\n",
    "\n",
    "# 将pivot_table中的NaN值填充为0\n",
    "pivot_table.fillna(0, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>单品编码</th>\n",
       "      <th>102900005115168</th>\n",
       "      <th>102900005115199</th>\n",
       "      <th>102900005115250</th>\n",
       "      <th>102900005115625</th>\n",
       "      <th>102900005115748</th>\n",
       "      <th>102900005115762</th>\n",
       "      <th>102900005115779</th>\n",
       "      <th>102900005115786</th>\n",
       "      <th>102900005115793</th>\n",
       "      <th>102900005115816</th>\n",
       "      <th>...</th>\n",
       "      <th>106956146480203</th>\n",
       "      <th>106957634300010</th>\n",
       "      <th>106957634300058</th>\n",
       "      <th>106958851400125</th>\n",
       "      <th>106971533450003</th>\n",
       "      <th>106971533455008</th>\n",
       "      <th>106971563780002</th>\n",
       "      <th>106972776821582</th>\n",
       "      <th>106973223300667</th>\n",
       "      <th>106973990980123</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>日期</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2020-07-01</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.88</td>\n",
       "      <td>6.72</td>\n",
       "      <td>3.19</td>\n",
       "      <td>9.24</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-07-02</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.93</td>\n",
       "      <td>4.23</td>\n",
       "      <td>3.18</td>\n",
       "      <td>9.19</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-07-03</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.57</td>\n",
       "      <td>4.61</td>\n",
       "      <td>3.79</td>\n",
       "      <td>9.20</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-07-04</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>5.77</td>\n",
       "      <td>3.46</td>\n",
       "      <td>9.16</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-07-05</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.48</td>\n",
       "      <td>5.77</td>\n",
       "      <td>3.45</td>\n",
       "      <td>10.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-06-26</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15.6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.33</td>\n",
       "      <td>5.76</td>\n",
       "      <td>2.42</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.96</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-06-27</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15.6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.34</td>\n",
       "      <td>5.74</td>\n",
       "      <td>2.47</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.95</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-06-28</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15.6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.37</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2.25</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.96</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-06-29</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15.6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.37</td>\n",
       "      <td>5.68</td>\n",
       "      <td>2.16</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.95</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-06-30</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15.6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.21</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2.15</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.95</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1091 rows × 251 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "单品编码        102900005115168  102900005115199  102900005115250  \\\n",
       "日期                                                              \n",
       "2020-07-01              0.0              0.0              0.0   \n",
       "2020-07-02              0.0              0.0              0.0   \n",
       "2020-07-03              0.0              0.0              0.0   \n",
       "2020-07-04              0.0              0.0              0.0   \n",
       "2020-07-05              0.0              0.0              0.0   \n",
       "...                     ...              ...              ...   \n",
       "2023-06-26              0.0              0.0             15.6   \n",
       "2023-06-27              0.0              0.0             15.6   \n",
       "2023-06-28              0.0              0.0             15.6   \n",
       "2023-06-29              0.0              0.0             15.6   \n",
       "2023-06-30              0.0              0.0             15.6   \n",
       "\n",
       "单品编码        102900005115625  102900005115748  102900005115762  \\\n",
       "日期                                                              \n",
       "2020-07-01              0.0              0.0             3.88   \n",
       "2020-07-02              0.0              0.0             3.93   \n",
       "2020-07-03              0.0              0.0             3.57   \n",
       "2020-07-04              0.0              0.0             0.00   \n",
       "2020-07-05              0.0              0.0             3.48   \n",
       "...                     ...              ...              ...   \n",
       "2023-06-26              0.0              0.0             2.33   \n",
       "2023-06-27              0.0              0.0             2.34   \n",
       "2023-06-28              0.0              0.0             2.37   \n",
       "2023-06-29              0.0              0.0             2.37   \n",
       "2023-06-30              0.0              0.0             2.21   \n",
       "\n",
       "单品编码        102900005115779  102900005115786  102900005115793  \\\n",
       "日期                                                              \n",
       "2020-07-01             6.72             3.19             9.24   \n",
       "2020-07-02             4.23             3.18             9.19   \n",
       "2020-07-03             4.61             3.79             9.20   \n",
       "2020-07-04             5.77             3.46             9.16   \n",
       "2020-07-05             5.77             3.45            10.00   \n",
       "...                     ...              ...              ...   \n",
       "2023-06-26             5.76             2.42             0.00   \n",
       "2023-06-27             5.74             2.47             0.00   \n",
       "2023-06-28             0.00             2.25             0.00   \n",
       "2023-06-29             5.68             2.16             0.00   \n",
       "2023-06-30             0.00             2.15             0.00   \n",
       "\n",
       "单品编码        102900005115816  ...  106956146480203  106957634300010  \\\n",
       "日期                           ...                                     \n",
       "2020-07-01              0.0  ...              0.0              0.0   \n",
       "2020-07-02              0.0  ...              0.0              0.0   \n",
       "2020-07-03              0.0  ...              0.0              0.0   \n",
       "2020-07-04              0.0  ...              0.0              0.0   \n",
       "2020-07-05              0.0  ...              0.0              0.0   \n",
       "...                     ...  ...              ...              ...   \n",
       "2023-06-26              0.0  ...              0.0              0.0   \n",
       "2023-06-27              0.0  ...              0.0              0.0   \n",
       "2023-06-28              0.0  ...              0.0              0.0   \n",
       "2023-06-29              0.0  ...              0.0              0.0   \n",
       "2023-06-30              0.0  ...              0.0              0.0   \n",
       "\n",
       "单品编码        106957634300058  106958851400125  106971533450003  \\\n",
       "日期                                                              \n",
       "2020-07-01              0.0              0.0             0.00   \n",
       "2020-07-02              0.0              0.0             0.00   \n",
       "2020-07-03              0.0              0.0             0.00   \n",
       "2020-07-04              0.0              0.0             0.00   \n",
       "2020-07-05              0.0              0.0             0.00   \n",
       "...                     ...              ...              ...   \n",
       "2023-06-26              0.0              0.0             1.96   \n",
       "2023-06-27              0.0              0.0             1.95   \n",
       "2023-06-28              0.0              0.0             1.96   \n",
       "2023-06-29              0.0              0.0             1.95   \n",
       "2023-06-30              0.0              0.0             1.95   \n",
       "\n",
       "单品编码        106971533455008  106971563780002  106972776821582  \\\n",
       "日期                                                              \n",
       "2020-07-01              0.0              0.0              0.0   \n",
       "2020-07-02              0.0              0.0              0.0   \n",
       "2020-07-03              0.0              0.0              0.0   \n",
       "2020-07-04              0.0              0.0              0.0   \n",
       "2020-07-05              0.0              0.0              0.0   \n",
       "...                     ...              ...              ...   \n",
       "2023-06-26              0.0              0.0              0.0   \n",
       "2023-06-27              0.0              0.0              0.0   \n",
       "2023-06-28              0.0              0.0              0.0   \n",
       "2023-06-29              0.0              0.0              0.0   \n",
       "2023-06-30              0.0              0.0              0.0   \n",
       "\n",
       "单品编码        106973223300667  106973990980123  \n",
       "日期                                            \n",
       "2020-07-01              0.0              0.0  \n",
       "2020-07-02              0.0              0.0  \n",
       "2020-07-03              0.0              0.0  \n",
       "2020-07-04              0.0              0.0  \n",
       "2020-07-05              0.0              0.0  \n",
       "...                     ...              ...  \n",
       "2023-06-26              0.0              0.0  \n",
       "2023-06-27              0.0              0.0  \n",
       "2023-06-28              0.0              0.0  \n",
       "2023-06-29              0.0              0.0  \n",
       "2023-06-30              0.0              0.0  \n",
       "\n",
       "[1091 rows x 251 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pivot_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 导出到Excel\n",
    "pivot_table.to_excel('./results/第三问/各单品按时间排列的批发价格.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 步骤3: 获取所有蔬菜单品\n",
    "vegetables = pivot_table.columns\n",
    "\n",
    "# 步骤3: 循环预测\n",
    "forecast_steps = 1  # 预测未来一天的成本加成定价\n",
    "\n",
    "# 创建一个空的DataFrame\n",
    "df = pd.DataFrame()\n",
    "\n",
    "for i in range(1, 246):  # 从索引值1循环到245\n",
    "    # 步骤4: 选择一个蔬菜单品进行分析\n",
    "    specific_vegetable_data = pivot_table[vegetables[i]]\n",
    "\n",
    "    # 步骤5: 建立ARIMA模型\n",
    "    model = ARIMA(specific_vegetable_data, order=(5, 1, 0))\n",
    "    model_fit = model.fit()\n",
    "\n",
    "    # 步骤6: 进行预测\n",
    "    forecast = model_fit.forecast(steps=forecast_steps)\n",
    "    rounded_forecast = round(forecast.values[0], 6)  # 提取数值部分并保留6位小数\n",
    "\n",
    "    # # 步骤7: 可视化结果\n",
    "\n",
    "    # plt.plot(specific_vegetable_data.index, specific_vegetable_data.values, label='Historical Daily Sales')\n",
    "    # plt.plot(pd.date_range(start=specific_vegetable_data.index[-1], periods=forecast_steps+1)[1:], forecast, label='Forecasted Daily Sales', color='red')\n",
    "    # plt.xlabel('Date')\n",
    "    # plt.ylabel('Sales (kg)')\n",
    "    # plt.title(f' {vegetables[i]} 批发价格预测')  # 根据索引的列名设置标题\n",
    "    # plt.legend()\n",
    "    # plt.show()\n",
    "\n",
    "    print(f' 7.1日{vegetables[i]} 批发价格预测量:', rounded_forecast)  # 根据索引的列名打印预测值\n",
    "\n",
    "\n",
    "    # 将每次循环的数据追加到df中\n",
    "    df = pd.concat([df, pd.DataFrame({'单品名称': [vegetables[i]], '7.1日批发价格预测量': [rounded_forecast]})], ignore_index=True) "
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.4"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
